package com.man;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.std.Book;
import com.std.DB;

public class BookCtrl {
	private DB db;
	public BookCtrl(){
		db=new DB();
	}
	public void insertItem(String title,String author,int typeId,String publisher,Date publishTime,
			String content,String picturePath,String isbn,double price,int totalAmount,int remainder) {
		String sql="insert into book_t values(book_s.nextval,?,?,?,?,?,?,?,?,?,?,?)";
		Object []params={title,author,typeId,publisher,publishTime,content,isbn,
				picturePath,price,totalAmount,remainder};
		db.execute(sql, params);
		
	}
	public int getCount(int typeId,String keyword) {
		String sql="select count(*) cnt from book_t,type_t where book_t.type_id=type_t.type_id ";
		Object []params=new Object[4];
		int length=0;
		if(typeId!=0){
			sql=sql+"and book_t.type_id=?";
			params[length++]=typeId;
		}
		
		if(keyword!=null&&keyword!=""&&keyword.length()!=0){
			System.out.println(keyword.length());
			sql=sql+" and ( title like '%"+keyword+"%' or author like '%"+keyword+
				"%' or type_name like '%"+keyword+"%' or publisher like '%"+keyword+
				"%' or content like '%"+keyword+"%' )";
		}	
		//System.out.println("sql:"+sql);
		db.execute(sql, params);
		ResultSet rs=db.getResultset();
		int cnt=0;
		try {
			if(rs.next()){
				cnt=rs.getInt("cnt");
			}			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return cnt;
	}
	public List<Book> getItems(int typeId,String keyword,int startPos,int endPos){
		List<Book> list=new ArrayList<Book>();
		String sql="select rownum cnt,book_t.*,type_name from book_t,type_t where book_t.type_id=type_t.type_id ";
		Object []params=new Object[4];
		int length=0;
		if(typeId!=0){
			sql=sql+"and book_t.type_id=?";
			params[length++]=typeId;
		}
		if(keyword!=null&&keyword!=""){
			sql=sql+"and ( title like '%"+keyword+"%' or author like '%"+keyword+
				"%' or type_name like '%"+keyword+"%' or publisher like '%"+keyword+
				"%' or content like '%"+keyword+"%' ) ";
		}
		if(startPos!=0&&endPos!=0&&startPos<endPos){
			sql="select * from ("+sql+") where cnt>=? and cnt<?";
			params[length++]=startPos;
			params[length++]=endPos;
		}
		db.execute(sql, params);
		ResultSet rs=db.getResultset();
		try {
			while(rs.next()){
				Book book=new Book();
				book.setAuthor(rs.getString("author"));
				book.setBookId(rs.getInt("book_id"));
				book.setContent(rs.getString("content"));
				book.setIsbn(rs.getString("isbn"));
				book.setPicturePath(rs.getString("picture_path"));
				book.setPrice(rs.getDouble("price"));
				book.setPublisher(rs.getString("publisher"));
				book.setPublishTime(rs.getDate("publish_time"));
				book.setRemainder(rs.getInt("remainder"));
				book.setTitle(rs.getString("title"));
				book.setTotalAmount(rs.getInt("total_amount"));
				book.setTypeId(rs.getInt("type_id"));
				book.setTypeName(rs.getString("type_name"));
				list.add(book);
			}		
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
		
		return list;
	}
	public Book getItem(int bookId) {
		String sql="select * from book_t,type_t where book_t.type_id=type_t.type_id ";
		Object []params=new Object[1];
		int length=0;
		if(bookId!=0){
			sql=sql+"and book_id=?";
			params[length++]=bookId;
		}
		db.execute(sql, params);
		ResultSet rs=db.getResultset();
		try {
			Book book=new Book();
			if(rs.next()){
				book.setAuthor(rs.getString("author"));
				book.setBookId(rs.getInt("book_id"));
				book.setContent(rs.getString("content"));
				book.setIsbn(rs.getString("isbn"));
				book.setPicturePath(rs.getString("picture_path"));
				book.setPrice(rs.getDouble("price"));
				book.setPublisher(rs.getString("publisher"));
				book.setPublishTime(rs.getDate("publish_time"));
				book.setRemainder(rs.getInt("remainder"));
				book.setTitle(rs.getString("title"));
				book.setTotalAmount(rs.getInt("total_amount"));
				book.setTypeId(rs.getInt("type_id"));
				book.setTypeName(rs.getString("type_name"));
			}			
			else {
				book=null;
			}
			
			return book;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}		
	}
	public void deleteItem(int bookId) {
		String sql="delete from book_t ";
		Object []params=new Object[1];
		int length=0;
		if(bookId!=0){
			sql=sql+"where book_id=?";
			params[length++]=bookId;
		}
		db.execute(sql, params);
		
	}
	public void updateItem(int bookId,String title,String author,int typeId,String publisher,Date publishTime,
			String content,String picturePath,String isbn,double price,int totalAmount,int remainder) {
		String sql="update book_t set title=?,author=?,type_id=?,publisher=?,publish_time=?,content=?,isbn=?,"+
				"price=?,total_amount=?,remainder=? where book_id=?";
		Object []params={title,author,typeId,publisher,publishTime,content,isbn,
				price,totalAmount,remainder,bookId};
		db.execute(sql, params);
		if(picturePath!=null&&picturePath!=""){
			sql="update book_t set picture_path=? where book_id=?";
			params[0]=picturePath;
			params[1]=bookId;
			params[2]=null;
			db.execute(sql, params);
		}
	}
	public void close() {
		db.close();
	}
}
